package CSISL4pk;

/**
 *
 * @author John Ho
 */
import java.awt.Color;
import java.awt.GridLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import javax.swing.*;
import java.sql.*;


public class SearchFrame extends JInternalFrame
{

    static int openFrameCount = 0;
    static final int xOffset = 60, yOffset = 60;

    //initialize components 
    JCheckBox dateChecked = new JCheckBox();
    JCheckBox typeChecked = new JCheckBox();
    JCheckBox areaChecked = new JCheckBox();
    JCheckBox statusChecked = new JCheckBox();
    JLabel searchLabel = new JLabel("Search");
    JLabel sDateLabel = new JLabel("Start Date");
    JLabel eDateLabel = new JLabel("End Date");
    JLabel typeLabel = new JLabel("Type");
    JLabel areaLabel = new JLabel("Area");
    JLabel statusLabel = new JLabel("Status");
    JTextField sDateField = new JTextField();
    JTextField eDateField = new JTextField();
    JComboBox typeCB = new JComboBox();
    JComboBox areaCB = new JComboBox();
    JComboBox statusCB = new JComboBox();
    JPanel searchP = new JPanel();
    //This Panel will hold the Fields
    JPanel fieldP = new JPanel();
    //The list will be added and bound to the search pane
    JList caseList = new JList();
    JButton searchButton = new JButton("Search");
    JButton loadButton = new JButton("Load");
    JButton resetButton = new JButton("Reset");
    //Array List for Case Objects
    //private ArrayList<Case> stuff = new ArrayList<Case>();
    Case temp = new Case();
    //private int arrayCounter = 0;

    public SearchFrame()
    {
        super("Search Window: " + (++openFrameCount),
                true, //resizable
                true, //closable
                true, //maximizable
                true);//iconifiable

        //...Create the GUI and put it in the window...

        this.add(searchP);        
        searchP.setLayout(null);

        searchLabel.setBounds(25, 25, 100, 25);
        searchButton.setBounds(200, 250, 100, 25);
        loadButton.setBounds(300, 250, 100, 25);        
        
        //Combo Box Lists
        typeCB.addItem("ARSON");
        typeCB.addItem("ASSAULT");
        typeCB.addItem("AUTO THEFT");
        typeCB.addItem("BATTERY");
        typeCB.addItem("BURGLARY");
        typeCB.addItem("DISORDERLY CONDUCT");
        typeCB.addItem("DUI");
        typeCB.addItem("MURDER");
        typeCB.addItem("ROBBERY");
        typeCB.addItem("SEXUAL ASSAULT");
        typeCB.addItem("THEFT");
        typeCB.addItem("VANDALISM");
        areaCB.addItem("Avenues");
        areaCB.addItem("Sugarhouse");
        areaCB.addItem("Liberty East");
        areaCB.addItem("Liberty West");
        areaCB.addItem("Trolley Square");
        areaCB.addItem("Central");
        areaCB.addItem("University");
        areaCB.addItem("Rose Park");
        areaCB.addItem("Glendale");
        areaCB.addItem("Foothill");
        areaCB.addItem("Capitol Hill");
        areaCB.addItem("Downtown");
        statusCB.addItem("Open");
        statusCB.addItem("Closed");

        //add components
        //Search Panel
        searchP.add(searchLabel);
        searchP.add(fieldP);
        searchP.add(searchButton);
        searchP.add(loadButton);
        
        //CHECK BOXES
        searchP.add( dateChecked);
        dateChecked.setBounds(160, 76, 20, 20);
        searchP.add( typeChecked);
        typeChecked.setBounds(160, 176, 20, 20);
        searchP.add(areaChecked);
        areaChecked.setBounds(160, 226, 20, 20);
        searchP.add(statusChecked);
        statusChecked.setBounds(160,276, 20, 20);
        
        //Field Panel        
        fieldP.setBounds(25, 50, 125, 275);
        fieldP.setLayout(new GridLayout(0, 1));
        fieldP.add(sDateLabel);
        fieldP.add(sDateField);
        fieldP.add(eDateLabel);
        fieldP.add(eDateField);
        fieldP.add(typeLabel);
        fieldP.add(typeCB);
        fieldP.add(areaLabel);
        fieldP.add(areaCB);
        fieldP.add(statusLabel);
        fieldP.add(statusCB);
        fieldP.add(resetButton);
        
        //Case List Panel
        searchP.add(caseList);
        caseList.setBounds(200, 75, 200, 175);
        caseList.setBackground(Color.white);
        

        setSize(600, 375);//size of window

        //Adding Button Listners
        searchButton.addActionListener(new search());
        resetButton.addActionListener(new reset());

        //Set the window's location.
        setLocation(400,150);
    }
    //Buttons Functionality

    private class search implements ActionListener
    {

        @Override
        public void actionPerformed(ActionEvent e)
        {
            // if date is selected... implement "only"
            
                try{
                    String[] temp = new String[100];
                    // create our mysql database connection
                    String myDriver = "com.mysql.jdbc.Driver";
                    String myUrl = "jdbc:mysql://www.db4free.net:3306/csisl";//www.freesql.org:3306/csisl?allowMultiQueries=true";
                    Class.forName(myDriver);
                    Connection conn = DriverManager.getConnection(myUrl, "csisaltlake","cs2450");
                    String query = "";
                    if(dateChecked.isSelected() & !typeChecked.isSelected() & !areaChecked.isSelected() & !statusChecked.isSelected()){
                        query = "SELECT caseNum from cases where invld = '0' and crimeDate between '"+sDateField.getText()+"' and '"+eDateField.getText()+"'";
                    }
                    //type
                    else if(!dateChecked.isSelected() & typeChecked.isSelected() & !areaChecked.isSelected() & !statusChecked.isSelected())
                    {
                        query = "SELECT caseNum from cases where invld = '0' and crimeType = '"+typeCB.getSelectedItem()+"'";
                    }
                    //area
                    else if(!dateChecked.isSelected() & !typeChecked.isSelected() & areaChecked.isSelected() & !statusChecked.isSelected()){
                        query = "SELECT caseNum from cases where invld = '0' and locale = '"+areaCB.getSelectedItem()+"'";
                    }
                    //status
                    else if(!dateChecked.isSelected() & !typeChecked.isSelected() & !areaChecked.isSelected() & statusChecked.isSelected()){
                        query = "Select caseNum from cases where invld = '0'";
                    }
                    //date and type  
                    else if(dateChecked.isSelected() & typeChecked.isSelected() & !areaChecked.isSelected() & !statusChecked.isSelected()){
                        query = "SELECT caseNum from cases where invld = '0' and crimeDate between '"+sDateField.getText()+"' and '"+eDateField.getText()+"'"
                                + "and crimeType = '"+typeCB.getSelectedItem()+"'";
                    }
                    //date area
                    else if(dateChecked.isSelected() & !typeChecked.isSelected() & areaChecked.isSelected() & !statusChecked.isSelected())
                    {
                        query = "SELECT caseNum from cases where invld = '0' and "
                                + "crimeDate between '"+sDateField.getText()+"' and '"+eDateField.getText()+"'"
                                + "and locale = '"+areaCB.getSelectedItem()+"'";
                    }
                    //date status
                    else if(dateChecked.isSelected() & !typeChecked.isSelected() & !areaChecked.isSelected() & statusChecked.isSelected())
                    {
                        query = "SELECT caseNum from cases where invld = '0' and "
                                + "crimeDate between '"+sDateField.getText()+"' and '"+eDateField.getText()+"'"
                                + "status = '"+statusCB.getSelectedItem()+"'";
                    }
                    //date type area
                    else if(dateChecked.isSelected() & typeChecked.isSelected() & areaChecked.isSelected() & !statusChecked.isSelected()){
                        
                    }
                    //date type area status
                    else if(dateChecked.isSelected() & typeChecked.isSelected() & areaChecked.isSelected() & statusChecked.isSelected()){
                        
                    }
                    //date status area
                    else if(dateChecked.isSelected() & !typeChecked.isSelected() & areaChecked.isSelected() & statusChecked.isSelected()){
                        
                    }
                    
                    
                    //type
                    
                    System.out.print(query);
                    // create the java statement
                    Statement st = conn.createStatement();
                    ResultSet rs = st.executeQuery(query);
                    int i = 0;
                    while (rs.next())
                        {

                            temp[i]= rs.getString("caseNum");
                            i++;

                        }
                    st.close();
                    caseList.removeAll();
                    caseList.setListData(temp);
                    caseList.revalidate();
                    caseList.repaint();
                }
                catch(Exception ex){
                    System.out.print(ex);
                }
            }
//            /*
//            This will be for testing purposes since we don't have information
//            in the SQL
//             */
//            caseList.removeAll();
//            String[] caseNumbers =
//            {
//                "11110000000", "1112", "1113", "1114", "1115"
//            };
//            caseList.setListData(caseNumbers);
//            caseList.revalidate();
//            caseList.repaint();
            
        }
    

    private class reset implements ActionListener
    {

        @Override
        public void actionPerformed(ActionEvent e)
        {
            sDateField.setText("");
            eDateField.setText("");
            typeCB.setSelectedIndex(0);
            areaCB.setSelectedIndex(0);
            statusCB.setSelectedIndex(0);
        }
    }
}